if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_updateConsumeOnDept')
          and type in ('P','PC'))
DROP PROCEDURE bd_proc_updateConsumeOnDept
GO
CREATE PROCEDURE [dbo].[bd_proc_updateConsumeOnDept]
  @selectedMonth AS varchar(7)
AS
BEGIN
	DECLARE @startDate varchar(10)--查询的开始日期（不含该天）
	DECLARE @endDate varchar(10) --查询的结束日期（含该天）
	DECLARE @thisMonth varchar(7)--本月
	DECLARE @consumeDate varchar(12)--消费日期
	DECLARE @sqlstr VARCHAR(MAX)--拼接的sql语句
	DECLARE @days varchar(10)--要更新或插入的天的字段名
	DECLARE @flag BIT --是否需要更新bd_sys_actionTime
	set @flag = 0;
	begin try
			begin tran
				--1.获取时间段（开始时间、结束时间）
				--开始时间的获取有两点，1，若@selectedMonth为'',则取bd_sys_actionTime中的action_time
				--若@selectedMonth不为''，则开始时间为@selectedMonth这月的1号
				--结束时间节点获取也有两点，若@selectedMonth为''，则取当天的前一天，若@selectedMonth不为''，
				--要看@selectedMonth是不是当月，若是当月，则取当天的前一天，若不是当月，则取@selectedMonth所在月的最后一天
				IF ''!= @selectedMonth BEGIN
				--@selectedMonth不为''
					set @startDate = @selectedMonth+'-01';
					--判断是否为当月
					set @thisMonth = CONVERT(varchar(7),GETDATE(),120);
					if @thisMonth = @selectedMonth BEGIN
						--本月
						set @endDate = CONVERT(varchar(10),getdate(),120);
						set @flag = 1;
					END
					ELSE BEGIN
					print @startDate
						--非本月
						set @endDate = convert(varchar(10),dateadd(day,-1,dateadd(month,1,convert(date,@startDate))));
					END
				END
				ELSE BEGIN
				--@selectedMonth为''
					select @startDate = convert(varchar(10),action_time,120) from bd_sys_actionTime where action_type = 1;
					set @endDate = convert(varchar(10),getdate(),120);
					set @flag = 1;
				END

				--2.先判断@selectedMonth为不为'',若是，删除表bd_rf_groupConsume 中月份为@selectedMonth的数据
				create table #bd_temp_consume(
					dep_serial numeric,
					user_serial numeric,
					sj varchar(10) COLLATE Chinese_PRC_CI_AS,
					meal_month varchar(7) COLLATE Chinese_PRC_CI_AS,
					meal_type varchar(50) COLLATE Chinese_PRC_CI_AS,
					cost_center varchar(64) COLLATE Chinese_PRC_CI_AS,
					user_type varchar(64) COLLATE Chinese_PRC_CI_AS,
					num int
				)
				--插入基础数据
				insert into #bd_temp_consume
				select
					a.dep_serial,
					a.user_serial,
					a.sj,
					SUBSTRING(a.sj,1,7),
					a.meal_type,
					a.cost_center,
					a.jcLx,
					sum(a.new_each) as num
				from (SELECT
					x.user_serial,
					x.dep_serial,
					convert(varchar(10),x.sj,120) as sj,
					x.new_each,
					d.cost_center,
					x.dev_time_no as meal_type,
					d.jcLx
				FROM
					xf_mx x
					inner join dt_user d on x.user_serial = d.user_serial
				WHERE
					CONVERT ( VARCHAR ( 10 ), x.sj, 120 ) between @startDate and @endDate
					and x.new_each != 0
					and x.new_each is not null
					and x.dep_serial is not null
					and x.dep_serial !=''
					and x.user_serial is not null
					and x.user_serial !=''
					) a
					GROUP BY a.user_serial,a.dep_serial,a.sj,a.cost_center,a.meal_type,a.jcLx;


				--根据@selectedMonth 分整月和平时
				IF ''!= @selectedMonth BEGIN
					--先删除老数据
					delete from bd_rf_groupConsume where meal_month = @selectedMonth;

					--再初始化当月的人员、部门、人员类型、成本中心、餐别
					insert into bd_rf_groupConsume(dep_serial,user_serial,user_type,cost_center,meal_type,meal_month) select dep_serial,user_serial,user_type,cost_center,meal_type,meal_month from #bd_temp_consume
					GROUP BY dep_serial,user_serial,user_type,cost_center,meal_type,meal_month;

				END
				else BEGIN
						--针对根据人员、部门、人员类型、成本中心、餐别不存在完全一样的记录，进行插入
						insert into bd_rf_groupConsume(dep_serial,user_serial,user_type,cost_center,meal_type,meal_month)
						select b.* from bd_rf_groupConsume a RIGHT JOIN
						(select dep_serial,user_serial,user_type,cost_center,meal_type,meal_month from #bd_temp_consume
					GROUP BY dep_serial,user_serial,user_type,cost_center,meal_type,meal_month) b on a.user_serial = b.user_serial and a.dep_serial=b.dep_serial and a.meal_month = b.meal_month and a.user_type = b.user_type and a.cost_center = b.cost_center and a.meal_type = b.meal_type
					where a.user_serial is null

				END

				DECLARE @i int;
				set @i = 0;
				while dateadd(day,@i,convert(date,@startDate))<=convert(date,@endDate)
					BEGIN
						--最后分天更新day1-day31间的就餐次数，并更新total字段。
						set @consumeDate = convert(varchar(10),dateadd(day,@i,convert(date,@startDate)));
						set @days = concat('day',Convert(NUMERIC,SUBSTRING(@consumeDate, 9, 2)));
							--更新
						set @consumeDate = ''''+@consumeDate+'''';
						set @sqlstr = concat('update a set ',@days,'= b.num,total = total+b.num-a.',@days,' from bd_rf_groupConsume a INNER JOIN #bd_temp_consume b on a.user_serial = b.user_serial and a.dep_serial=b.dep_serial and a.meal_month = b.meal_month and a.user_type = b.user_type and a.cost_center = b.cost_center and a.meal_type = b.meal_type where b.sj = ',@consumeDate);

						--执行语句
						exec(@sqlstr);
						set @i+=1
					END

				--5.若结束时间是当天的前一天，则更新表bd_sys_actionTime
				if @flag=1 BEGIN
					update bd_sys_actionTime set action_time = getdate();
				END
			commit tran
	end try
	begin catch
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			  RAISERROR (@errStr, 16, 1);
			END;
	end catch
	drop table #bd_temp_consume;
END